package com.example.studentmanager.db;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import androidx.annotation.Nullable;

import com.example.studentmanager.App;
import com.example.studentmanager.db.entity.Course;
import com.example.studentmanager.db.entity.Student;

import java.util.ArrayList;
import java.util.List;

public class MyDBHelper extends SQLiteOpenHelper {
    private static final String DB_NAME = "Storage.db";
    private static final int DB_VERSION = 1;
    private static final MyDBHelper INSTANCES = new MyDBHelper(App.context);
    private SQLiteDatabase db;

    public static MyDBHelper getInstances() {
        return INSTANCES;
    }

    private MyDBHelper(@Nullable Context context) {
        super(context, DB_NAME, null, DB_VERSION);
        db = getWritableDatabase();
    }


    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase) {
        createTable(sqLiteDatabase);
    }

    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {

    }

    private void createTable(SQLiteDatabase sqLiteDatabase) {
//      学生表
        sqLiteDatabase.execSQL("CREATE TABLE IF NOT EXISTS Student(id INTEGER PRIMARY KEY,username TEXT unique,password TEXT,name TEXT,clazz TEXT,numberId TEXT,loginStatus INT);");

//      课程表
        sqLiteDatabase.execSQL("CREATE TABLE IF NOT EXISTS Course(id INTEGER PRIMARY KEY,name TEXT unique,teacher TEXT);");

    }

    /**
     * 查询是否有这个用户
     *
     * @param username
     * @return
     */
    public boolean isExistsStudent(String username) {
        Cursor cursor = db.rawQuery("select count(*) from Student where username = ?;", new String[]{username});
        if (cursor != null) {
            cursor.moveToFirst();
            boolean result = cursor.getInt(0) > 0;
            cursor.close();
            return result;
        }
        return false;
    }

    /**
     * 注册用户
     *
     * @param username
     * @param password
     * @param name
     * @param clazz
     * @param numberId
     * @return
     */
    public boolean registerStudent(String username, String password, String name, String clazz, String numberId) {
        ContentValues values = new ContentValues();
        values.put("username", username);
        values.put("password", password);
        values.put("name", name);
        values.put("clazz", clazz);
        values.put("numberId", numberId);
        values.put("loginStatus", 0);
        long account = db.insert("Student", null, values);
        return account > 0;
    }

    /**
     * 是否登录
     *
     * @return
     */
    public Student isLogin() {
        Cursor cursor = db.rawQuery("select * from Student where loginStatus = ?;", new String[]{"1"});
        Student student = null;
        if (cursor != null) {
            try {
                if (cursor.getCount() > 0) {
                    cursor.moveToFirst();
                    student = new Student();
                    student.setUsername(cursor.getString(cursor.getColumnIndexOrThrow("username")));
                    student.setPassword(cursor.getString(cursor.getColumnIndexOrThrow("password")));
                    student.setName(cursor.getString(cursor.getColumnIndexOrThrow("name")));
                    student.setClazz(cursor.getString(cursor.getColumnIndexOrThrow("clazz")));
                    student.setNumberId(cursor.getString(cursor.getColumnIndexOrThrow("numberId")));
                    student.setLogin(cursor.getInt(cursor.getColumnIndexOrThrow("loginStatus")) == 1);

                }
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                cursor.close();
            }
        }
        return student;
    }

    /**
     * 登录
     *
     * @param username
     * @param password
     * @return
     */
    public boolean login(String username, String password) {
        ContentValues values = new ContentValues();
        values.put("loginStatus", 1);
        int account = db.update("Student", values, "username = ? and password = ?", new String[]{username, password});
        return account > 0;
    }

    /**
     * 登出
     *
     * @param user
     * @return
     */
    public boolean logout(Student user) {
        ContentValues values = new ContentValues();
        values.put("loginStatus", 0);
        int account = db.update("Student", values, "username = ?", new String[]{user.getUsername()});
        return account > 0;
    }

    public boolean isExistsCourse(Course course) {
        Cursor cursor = db.rawQuery("select count(*) from Course where name = ?;", new String[]{course.getName()});
        if (cursor != null) {
            cursor.moveToFirst();
            boolean result = cursor.getInt(0) > 0;
            cursor.close();
            return result;
        }
        return false;
    }

    public boolean addCourse(Course course) {
        ContentValues values = new ContentValues();
        values.put("name", course.getName());
        values.put("teacher", course.getTeacher());
        long account = db.insert("Course", null, values);
        return account > 0;
    }

    public List<Course> findAllCourse() {
        Cursor cursor = db.rawQuery("select * from Course;", new String[]{});
        List<Course> courses = new ArrayList<>();
        if (cursor != null) {
            try {
                while (cursor.moveToNext()) {
                    cursor.moveToFirst();
                    Course course = new Course();
                    course.setName(cursor.getString(cursor.getColumnIndexOrThrow("name")));
                    course.setTeacher(cursor.getString(cursor.getColumnIndexOrThrow("teacher")));
                    courses.add(course);
                }
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                cursor.close();
            }
        }
        return courses;
    }
}
